const connection = require("../app/database");

class MomentService {
  // 接收用户id和动态内容
  async create(usesId, content) {
    //   SQL
    const statement = `INSERT INTO moment (user_id,content) VALUES (?,?);`;
    const result = await connection.execute(statement, [usesId, content]);
    return result;
  }
  //   查询单个内容详情
  async getMomentById(id) {
    //  查询数据库语句
    // 涉及到一对多,并且要获取的是完整的内容信息(包括用户的详细信息)，
    // 所以要用多表连接中的左连接
    // 返回的数据关于用户信息的那一块要用对象形式，所以用JSON_OBJECT
    const statement = `
      SELECT 
      m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
      JSON_OBJECT('id', u.id, 'name', u.name,'avatar_url', u.avatar_url) author,
      IF(COUNT(l.id),JSON_ARRAYAGG(
        JSON_OBJECT('id', l.id, 'name', l.name)
      ),NULL) labels,
      (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
        JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt,
                    'user', JSON_OBJECT('id', cu.id, 'name', cu.name,'avatar_url', cu.avatar_url))
      ),NULL) FROM comment c LEFT JOIN users cu ON c.user_id = cu.id WHERE m.id = c.moment_id) comments,
      (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8888/moment/images/', file.filename)) 
      FROM file WHERE m.id = file.moment_id) images
      FROM moment m
      LEFT JOIN users u ON m.user_id = u.id
      LEFT JOIN moment_label ml ON m.id = ml.moment_id
      LEFT JOIN label l ON ml.label_id = l.id
      WHERE m.id = ?
      GROUP BY m.id;  
    `;
    const [result] = await connection.execute(statement, [id]);
    return result[0];
  }

  //   查询多个内容
  async getMomentList(offset, size) {
    const statement = `
        SELECT 
        m.id id,m.content content, m.createAt createTime,m.updateAt updateTime,
        JSON_OBJECT('id',u.id,'name',u.name) author,
        (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount,
        (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8888/moment/images/', file.filename)) 
        FROM file WHERE m.id = file.moment_id) images
        FROM moment m
        LEFT JOIN users u ON m.user_id = u.id
        LIMIT ?,?;
      `;

    const [result] = await connection.execute(statement, [offset, size]);

    return result;
  }
  async update(content, momentId) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`;
    const [result] = await connection.execute(statement, [content, momentId]);
    return result;
  }
  async remove(momentId) {
    const statement = `DELETE FROM moment WHERE id = ?`;
    const [result] = await connection.execute(statement, [momentId]);
    return result;
  }

  async hasLabel(momentId, labelId) {
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?`;
    const [result] = await connection.execute(statement, [momentId, labelId]);
    return result[0] ? true : false;
  }

  async addLabel(momentId, labelId) {
    const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`;
    const [result] = await connection.execute(statement, [momentId, labelId]);
    return result;
  }
}
module.exports = new MomentService();
